package org.hismig.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.hismig.cfg.DBConnection;

/**
 * @declare: //TODO <br />
 *           This class created by Vansion.Hopihit on 2012-07-04 <br />
 *           <a href="mailto:vansionhopihit@gmail.com">Contact Me</a>
 * @version 1.0
 * @author Vansion.Hopihit
 * 
 */
public class JDBCTool {

	public static void rollbackQuietly(Connection conn) {
		if (null != conn) {
			try {
				if (!conn.isClosed()) {
					conn.rollback();
				}
			} catch (SQLException e) {
			}
		}
	}

	public static String getPreparedInsertSql(String tableName,
			ResultSetMetaData rsmd) throws SQLException {

		int colCount = rsmd.getColumnCount();

		StringBuilder colNames = new StringBuilder();
		StringBuilder colPlace = new StringBuilder();

		for (int i = 1; i <= colCount; i++) {
			colNames.append(rsmd.getColumnName(i));
			colPlace.append("?");

			if (i < colCount) {
				colNames.append(",");
				colPlace.append(",");
			}
		}

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append("(");
		sql.append(colNames.toString());
		sql.append(")");
		sql.append(" VALUES ");
		sql.append("(");
		sql.append(colPlace.toString());
		sql.append(")");

		return sql.toString();
	}

	public static void checkAndCreateTable(Connection conn_new,
			String tableName, ResultSet rs_old) throws SQLException {

		if (!checkTableExsit(conn_new, tableName)) {
			createTableByRS(tableName, rs_old, conn_new);
		}
	}

	public static boolean checkTableExsit(Connection conn, String tableName)
			throws SQLException {
		CheckTool.checkNotNull(conn);
		CheckTool.checkNotEmpty(tableName);

		ResultSet rs_check = conn.getMetaData().getTables(null, null,
				tableName, new String[]{"TABLE"});

		return rs_check.next();

	}

	@SuppressWarnings("static-access")
	public static void createTableByRS(String tableName, ResultSet rs_old,
			Connection conn_new) throws SQLException {
		CheckTool.checkNotEmpty(tableName);
		CheckTool.checkNotNull(rs_old);
		CheckTool.checkNotNull(conn_new);

		ResultSetMetaData rsmd = rs_old.getMetaData();

		StringBuffer sb_createSql = new StringBuffer();

		sb_createSql.append(String.format("CREATE TABLE %s (", tableName));

		for (int i = 1; i <= rsmd.getColumnCount(); i++) {

			sb_createSql.append(rsmd.getColumnName(i));
			sb_createSql.append(" ");
			sb_createSql.append(rsmd.getColumnTypeName(i));

			if (!rsmd.isSigned(i)) {
				sb_createSql.append("(");
				sb_createSql.append(rsmd.getPrecision(i));
				sb_createSql.append(")");
			}

			if (rsmd.columnNoNulls == rsmd.isNullable(i)) {
				sb_createSql.append(" NOT NULL");
			}

			if (i < rsmd.getColumnCount()) {
				sb_createSql.append(",");
			} else {
				sb_createSql.append(")");
			}
		}

		Statement stmt = null;
		try {
			stmt = conn_new.createStatement();
			stmt.executeUpdate(sb_createSql.toString());
		} catch (SQLException e) {
			throw e;
		} finally {
			closeQuietly(stmt);
		}
	}

	public static void closeQuietly(Connection conn) {
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
			} finally {
				conn = null;
			}
		}
	}

	public static void closeQuietly(ResultSet rs) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
			} finally {
				rs = null;
			}
		}
	}

	public static void closeQuietly(Statement stmt) {
		if (null != stmt) {
			try {
				stmt.close();
			} catch (SQLException e) {
			} finally {
				stmt = null;
			}
		}
	}

	public static void closeQuietly(PreparedStatement pstm) {
		if (null != pstm) {
			try {
				pstm.close();
			} catch (SQLException e) {
			} finally {
				pstm = null;
			}
		}
	}

	public static void closeQuietly(Connection conn, Statement stmt,
			ResultSet rs) {
		closeQuietly(rs);
		closeQuietly(stmt);
		closeQuietly(conn);
	}

	public static void closeQuietly(Connection conn, PreparedStatement pstm,
			ResultSet rs) {
		closeQuietly(rs);
		closeQuietly(pstm);
		closeQuietly(conn);
	}

	public static void closeQuietly(DataSource dataSource) {

		if (null != dataSource) {
			if (dataSource instanceof BasicDataSource) {
				try {
					((BasicDataSource) dataSource).close();
				} catch (SQLException e) {
				}
			}
		}

		dataSource = null;

	}

	public static DataSource getDataSource(DBConnection dbConnection) {
		if (null != dbConnection) {

			String driver = dbConnection.getParamByName("driver");
			String url = dbConnection.getParamByName("url");
			String user = dbConnection.getParamByName("user");
			String password = dbConnection.getParamByName("password");

			if (dbConnection.isEncrypt()) {
				user = EncryptTool.decrypt(user);
				password = EncryptTool.decrypt(password);
			}

			// use the dhcp database connect pool
			BasicDataSource dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driver);
			dataSource.setUrl(url);
			dataSource.setUsername(user);
			dataSource.setPassword(password);

			return dataSource;
		}
		return null;
	}

}
